/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package dao;

import DataAccess.DBUtil;
import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import javax.swing.JOptionPane;

/**
 *
 * @author phuong
 */
public class Jobseeker {
    public static ArrayList<Entity.Jobseeker> getAll(){
        String sql = "{call pcGetAllJobseeker}";
        ArrayList<Entity.Jobseeker> arrCom = new ArrayList<Entity.Jobseeker>();

        try{
            CallableStatement cs = DBUtil.getConnection(DBUtil.getInfoDB()).prepareCall(sql);

            ResultSet rsJob = cs.executeQuery();
            while(rsJob.next()){
                Entity.Jobseeker job = new Entity.Jobseeker();
                job.setJobseekerID(rsJob.getInt("JobseekerID"));
                job.setName(rsJob.getString("Name"));
                job.setClassificationID(rsJob.getInt("ClassificationID"));
                job.setDateOfBirth(rsJob.getDate("DateOfBirth"));
                job.setSex(rsJob.getBoolean("Gender"));
                job.setAddress(rsJob.getString("Address"));
                job.setLocation(rsJob.getString("Location"));
                job.setPhoneNumber(rsJob.getString("PhoneNumber"));
                job.setSalary(rsJob.getFloat("Salary"));
                job.setSkill(rsJob.getString("Skill"));
                job.setExperience(rsJob.getString("Experience"));
                job.setEducation(rsJob.getString("Education"));
                job.setCertificate(rsJob.getString("Certificate"));
                job.setNote(rsJob.getString("Note"));
                job.setEmail(rsJob.getString("Email"));
                arrCom.add(job);

            }
        } catch (Exception ex) {
            System.out.println(ex.getMessage());
        }
            return arrCom;
    }

    public static ArrayList<Entity.TopJobseeker> getTops(){
        String sql = "{call pcGetTopJobseeker}";
        ArrayList<Entity.TopJobseeker> arrCom = new ArrayList<Entity.TopJobseeker>();

        try{
            CallableStatement cs = DBUtil.getConnection(DBUtil.getInfoDB()).prepareCall(sql);

            ResultSet rsJobseeker = cs.executeQuery();
            while(rsJobseeker.next()){
                Entity.TopJobseeker topjob = new Entity.TopJobseeker();
                topjob.setJobseekerID(rsJobseeker.getInt("JobseekerID"));
                topjob.setName(rsJobseeker.getString("Name"));
                topjob.setSpeciality(rsJobseeker.getString("Speciality"));
                topjob.setSex(rsJobseeker.getBoolean("Gender"));

                arrCom.add(topjob);

            }
        } catch (Exception ex) {
            System.out.println(ex.getMessage());
        }
            return arrCom;
    }

    public static boolean Insert(Entity.Jobseeker j){
        String sql = "{call pcInsertJobseeker(?,?,?,?,?,?,?,?,?,?,?,?,?,?)}";
        try{
            CallableStatement cs = DBUtil.getConnection(DBUtil.getInfoDB()).prepareCall(sql);

            cs.setString(1,j.getName());
            cs.setInt(2,j.getClassificationID());
            cs.setDate(3,new java.sql.Date(j.getDateOfBirth().getTime()));
            cs.setBoolean(4,j.isSex());
            cs.setString(5,j.getAddress());
            cs.setString(6,j.getLocation());
            cs.setString(7,j.getPhoneNumber());
            cs.setString(8,j.getEmail());
            cs.setFloat(9,j.getSalary());
            cs.setString(10,j.getSkill());
            cs.setString(11,j.getExperience());
            cs.setString(12,j.getEducation());
            cs.setString(13,j.getCertificate());
            cs.setString(14,j.getNote());

            return (cs.executeUpdate()==1);
        }catch(Exception ex){
            JOptionPane.showMessageDialog(null, ex.getMessage());
            return false;
        }
    }

    public static boolean Update(Entity.Jobseeker j){
        String sql = "{call pcUpdateJobseeker(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}";
        try{
            CallableStatement cs = DBUtil.getConnection(DBUtil.getInfoDB()).prepareCall(sql);

            cs.setInt(1,j.getJobseekerID());
            cs.setString(2,j.getName());
            cs.setInt(3,j.getClassificationID());
            cs.setDate(4,new java.sql.Date(j.getDateOfBirth().getTime()));
            cs.setBoolean(5,j.isSex());
            cs.setString(6,j.getAddress());
            cs.setString(7,j.getLocation());
            cs.setString(8,j.getPhoneNumber());
            cs.setString(9,j.getEmail());
            cs.setFloat(10,j.getSalary());
            cs.setString(11,j.getSkill());
            cs.setString(12,j.getExperience());
            cs.setString(13,j.getEducation());
            cs.setString(14,j.getCertificate());
            cs.setString(15,j.getNote());

            return (cs.executeUpdate()==1);
        }catch(Exception ex){
            //JOptionPane.showMessageDialog(null, ex.getMessage());
            return false;
        }
    }

    public static boolean Delete(Entity.Jobseeker j){
        String sql = "{call pcDeleteJobseeker(?)}";
        try{
            CallableStatement cs = DBUtil.getConnection(DBUtil.getInfoDB()).prepareCall(sql);

            cs.setInt(1,j.getJobseekerID());

            return (cs.executeUpdate()==1);
        }catch(Exception ex){
            return false;
        }
    }

    public static ArrayList<Entity.Jobseeker> getAllJobseeker(){
        String sql = "{call pcGetAllJobseeker}";
        ArrayList<Entity.Jobseeker> arrCom = new ArrayList<Entity.Jobseeker>();

        try{
            CallableStatement cs = DBUtil.getConnection(DBUtil.getInfoDB()).prepareCall(sql);

            ResultSet rsJobseeker = cs.executeQuery();
            while(rsJobseeker.next()){
                Entity.Jobseeker j = new Entity.Jobseeker();

                j.setName(rsJobseeker.getString("Name"));

                arrCom.add(j);
            }
        } catch (Exception ex) {
            System.out.println(ex.getMessage());
        }
            return arrCom;
    }

    public static int  getJobseekerID(String name){
            String sql = "select JobseekerID from Jobseeker where name = " + "N'" + name + "'" ;

       int JobseekerID = 0 ;

        try{
            CallableStatement cs = DBUtil.getConnection(DBUtil.getInfoDB()).prepareCall(sql);

            ResultSet rsJobseekerID = cs.executeQuery();

                while(rsJobseekerID.next())
                JobseekerID = rsJobseekerID.getInt("JobseekerID");


        } catch (Exception ex) {
            System.out.println(ex.getMessage());
        }
            return JobseekerID;
    }

    public static String  getJobseekerName(int ID){
       String sql = "select Name from Jobseeker where JobseekerID = "  + ID   ;

       String name = null  ;

        try{
            CallableStatement cs = DBUtil.getConnection(DBUtil.getInfoDB()).prepareCall(sql);

            ResultSet rsJobseekerName = cs.executeQuery();

                while(rsJobseekerName.next())
                name = rsJobseekerName.getString("Name");


        } catch (Exception ex) {
            System.out.println(ex.getMessage());
        }
            return name;
    }


}
